<!DOCTYPE html>
<html>
    <head>
        <title>Pick-up Game</title>
        <link rel="stylesheet" href="style.css" type="text/css" />
        <script src="http://code.jquery.com/jquery-latest.js"></script>
	<script type="text/javascript" src="http://jzaefferer.github.com/jquery-validation/jquery.validate.js"></script>
	<script>
	    $(document).ready(function(){
		$("#gamesearch").validate();
	    });
	</script>
    </head>

    <body>

        <?php
        include_once('menu.php');

        // connect to database
        $conn_str = 'host=dbsrv1.cdf.toronto.edu dbname=csc309g23 user=csc309g23 password=ipheid5h';
        $conn = pg_connect($conn_str);

        // query database
        $query = 'SELECT * FROM sports';
        $result = pg_query($conn, $query);
        ?>
        
        <div class="container">
        
        <h2>Games</h2>
        <hr></hr>
	<form action="games.php?sug" method="post" id="suggestform">
		<table>
		    <tr><td>Suggest Games:</td><td><input type="submit" name="sugsearch" value="Find me some matches!"></td></tr>
		 </table>
	</form>
        <form action="games.php?go" method="post" id="gamesearch">
                <table>
                    <tr><td>Sport</td><td><select name="sport">
				    <option>All</option>
                                <?php while ($row = pg_fetch_row($result)) { ?>
                                    <option><?= $row[0] ?></option>
                                <?php } ?>
				    
				</select></td></tr>
                    <tr><td>Skill</td><td><select name="skill">
				<option>All</option>
                                <option>0-1</option>
                                <option>1-2</option>
                                <option>2-3</option>
                                <option>3-4</option>
                                <option>4-5</option>
                            </select></td>
                    </tr>
                    <tr><td>City</td><td><input type="text" name="city"></td></tr>
                    
                            <?php // create countries dropdown
			    $query = 'SELECT * FROM countries';
			    $result = pg_query($conn, $query);
			    ?>
			    <tr><td>Country</td>
                                <td><select name="country">
				    <option value=''>All</option>
                                        <?php while ($row = pg_fetch_row($result)) { ?>
                                            <option><?= $row[0] ?></option>
                                        <?php } ?>
                                    </select>
                            </td></tr>
                            
                    <tr><td>Organizer</td><td><input type="text" name="organizer"></td></tr>
                    <tr><td></td><td><input type="submit" name="submit" value="Search"></td></tr>
                </table>
        </form>
	<?php
	if(isset($_POST['sugsearch'])){
	?>
	    <h2>Results</h2>
	    <hr></hr>
                <table class="displayTable">
                    <tr>
                        <td>Sport </td>
                        <td>Skill</td>
                        <td>Organizer</td>
                        <td>City</td>
                        <td>Country</td>
			<td>Day</td>
                        <td>Start</td>
                        <td>End</td>
                    </tr>

                    <?php

                    // query database to display all the 
		    
		    
		    $sugq = 'select sport,games.skill,rosters.username AS organizerID,city,country,games.start,games.endtime,to_char(games.start,\'day\') AS gameday,"time"(games.start) AS gametime,schedule.day AS userday,schedule.start AS userstart, schedule.endtime AS userend, schedule.username AS playername FROM (games INNER JOIN rosters ON (games.gameid=rosters.gameid)) INNER JOIN schedule ON (schedule.day=to_char(games.start,\'day\')) WHERE organizer=TRUE AND ("time"(games.start) >= schedule.start - interval \'1 hour\') AND ("time"(games.start) <= schedule.endtime - interval \'1 hour\') AND sport IN (SELECT sport FROM availability WHERE username=\''.$_SESSION['username'].'\') AND (country = \''.$_SESSION['country'].'\') AND (city = \''.$_SESSION['city'].'\') AND (schedule.username = \''.$_SESSION['username'].'\')';
		    
		    
		    //$query = 'SELECT sport,games.skill,username,city,country,start,endtime,to_char(start,\'day\') AS day FROM games INNER JOIN rosters ON (games.gameid=rosters.gameid) WHERE organizer = TRUE AND sport LIKE \''.$sportfield.'\' AND games.skill LIKE \''.$skillfield.'\' AND rosters.username LIKE \''.$orgfield.'\''.$loccheck;
                    //$query = 'SELECT sport,skill,organizer,city,country,start,end FROM games INNER JOIN rosters ON (games.gameid=rosters.gameid) WHERE sport=\''.$_POST['sport'].'\' AND skill=\''.$_POST['skill'].'\'';
                    //$query = 'SELECT * FROM games';
                    $result = pg_query($conn, $sugq);
		    
                    while ($game = pg_fetch_assoc($result)) {                ?>
			
                        <tr class="hand" onClick="location.href='gameDetails.php?gameid=<?= $game['gameid'] ?>';">
                            <td><?= $game['sport'] ?></td>
                            <td><?= $game['skill'] ?></td>
                            <td><?= $game['organizerid'] ?></td>
                            <td><?= $game['city'] ?></td>
                            <td><?= $game['country'] ?></td>
			    <td><?= $game['gameday'] ?></td>
                            <td><?= $game['start'] ?></td>
                            <td><?= $game['endtime'] ?></td>
			    
                        </tr>
			
                    <?php } ?>
                </table>  
        <?php } 	?>
	
        <?php
        if(isset($_POST['submit'])){
        ?>
	    <h2>Results</h2>
	    <hr></hr>
                <table class="displayTable">
                    <tr>
                        <td>Sport</td>
                        <td>Skill</td>
                        <td>Organizer</td>
                        <td>City</td>
                        <td>Country</td>
			<td>Day</td>
                        <td>Start</td>
                        <td>End</td>
                    </tr>

                    <?php
                    // query database to display all the 
		    $sportfield='%';
		    $skillfield='%';
		    $orgfield='%';
		    $loccheck='';
		    $cityfield='%';
		    $countryfield='%';

		    if (isset($_POST['sport'])){
			
			if($_POST['sport']!='All'){ $sportfield=$_POST['sport']; }
		    }
		    if (isset($_POST['skill'])){
			
			if($_POST['skill']!='All'){ $skillfield=$_POST['skill']; }
		    }
		    if (isset($_POST['organizer'])){
			
			if($_POST['organizer']!=''){ $orgfield=$_POST['organizer']; }
		    }
		    if (isset($_POST['city'])||isset($_POST['country'])){			
			if($_POST['city']!=''){ $loccheck = $loccheck.'AND city=\''.$_POST['city'].'\''; }
			if($_POST['country']!=''){ $loccheck = $loccheck.'AND country=\''.$_POST['country'].'\''; }
		    }
		    if (isset($_POST['country'])){
			
			if($_POST['country']!=''){ $countryfield=$_POST['country']; }
		    }

		    $query = 'SELECT sport,games.skill,username,city,country,start,endtime,to_char(start,\'day\') AS day FROM games INNER JOIN rosters ON (games.gameid=rosters.gameid) WHERE organizer = TRUE AND sport LIKE \''.$sportfield.'\' AND games.skill LIKE \''.$skillfield.'\' AND rosters.username LIKE \''.$orgfield.'\''.$loccheck;
                    //$query = 'SELECT sport,skill,organizer,city,country,start,end FROM games INNER JOIN rosters ON (games.gameid=rosters.gameid) WHERE sport=\''.$_POST['sport'].'\' AND skill=\''.$_POST['skill'].'\'';
                    //$query = 'SELECT * FROM games';
                    $result = pg_query($conn, $query);

                    while ($game = pg_fetch_assoc($result)) {
                        ?>

                        <tr class="hand" onClick="location.href='gameDetails.php?gameid=<?= $game['gameid'] ?>';">
                            <td><?= $game['sport'] ?></td>
                            <td><?= $game['skill'] ?></td>
                            <td><?= $game['username'] ?></td>                           
                            <td><?= $game['city'] ?></td>
                            <td><?= $game['country'] ?></td>
			    <td><?= $game['day'] ?></td>
                            <td><?= $game['start'] ?></td>
                            <td><?= $game['endtime'] ?></td>
			    
                        </tr>
                    <?php } ?>
                </table>  
        <?php
        } elseif(!isset($_POST['sugsearch'])) { ?>
            <h2>Results</h2>
            <hr></hr>
                <table class="displayTable">
                    <tr>
                        <td>Sport</td>
                        <td>Skill</td>
                        <td>Organizer</td>                        
                        <td>City</td>
                        <td>Country</td>
                        <td>Start</td>
                        <td>End</td>
                    </tr>

                    <?php
                    // query database to display all the games
                    //$query = 'SELECT * FROM games WHERE sport=\''.$_POST['sport'].'\' AND skill=\''.$_POST['skill'].'\'';
                    $query = 'SELECT *,games.skill AS gameskill FROM games INNER JOIN rosters ON (games.gameid=rosters.gameid) WHERE organizer = TRUE';
                    $result = pg_query($conn, $query);

                    while ($game = pg_fetch_assoc($result)) {
                        ?>

                        <tr class="hand" onClick="location.href='gameDetails.php?gameid=<?= $game['gameid'] ?>';">
                            <td><?= $game['sport'] ?></td>
                            <td><?= $game['gameskill'] ?></td>
                            <td><?= $game['username'] ?></td>
                            
                            <td><?= $game['city'] ?></td>
                            <td><?= $game['country'] ?></td>
                            <td><?= $game['start'] ?></td>
                            <td><?= $game['endtime'] ?></td>
                        </tr>
                    <?php } ?>
                </table>    
        <?php } ?>
        
        
        </div>
    </body>

</html>
